import re
import time
from datetime import datetime
from io import StringIO

import shortuuid
from pymysql import MySQLError

from server.baseservice import BaseService
from util.filehandler import ExcelHandler, CsvHandler
from util.sqlhelper import SQLHelper
import warnings

warnings.simplefilter(action='ignore', category=FutureWarning)


def update_tickers_from_excel(file):
    eh = ExcelHandler(file)
    df = eh.transfer_tendency()
    df_list = [(row['TICKER_CODE'], row['TICKER_NAME'], row['DAY20_RISE'], row['LIST_DATE'], row['LEVEL_NAME'],
               row['LEVEL_DETAIL'], row['FLOAT_MARKET_CAP'],row['UPDATE_TIME'])for index, row in df.iterrows()]
    try:
        db = SQLHelper()
        with db as (conn, cursor):
            sql_str = (
                "replace into tendency(TICKER_CODE, TICKER_NAME, DAY20_RISE, LIST_DATE, LEVEL_NAME, LEVEL_DETAIL, "
                "FLOAT_MARKET_CAP, UPDATE_TIME) VALUES(%s, %s, %s, %s, %s, %s, %s, %s)")
            cursor.executemany(sql_str,  df_list)
            conn.commit()
    except Exception as e:
        print(e)

def update_tickers_from_csv(file):
    eh = CsvHandler(file)
    df = eh.transfer_df()
    df_list = [(row['TICKER_CODE'], row['TICKER_NAME'], row['DAY20_RISE'], row['LIST_DATE'], row['LEVEL_NAME'],
               row['LEVEL_DETAIL'], row['FLOAT_MARKET_CAP'],row['UPDATE_TIME'])for index, row in df.iterrows()]
    try:
        db = SQLHelper()
        with db as (conn, cursor):
            sql_str = (
                "replace into tendency(TICKER_CODE, TICKER_NAME, DAY20_RISE, LIST_DATE, LEVEL_NAME, LEVEL_DETAIL, "
                "FLOAT_MARKET_CAP, UPDATE_TIME) VALUES(%s, %s, %s, %s, %s, %s, %s, %s)")
            cursor.executemany(sql_str,  df_list)
            conn.commit()
    except Exception as e:
        print(e)

def update_category():
    try:
        db = SQLHelper()
        with db as (conn, cursor):
            select_sql = "SELECT LEVEL_NAME, COUNT(*) as TICKER_COUNT, UPDATE_TIME FROM tendency GROUP BY LEVEL_NAME"
            cursor.execute(select_sql)
            results = cursor.fetchall()

            replace_sql = ("replace into category(LEVEL_NAME, TICKER_COUNT, UPDATE_TIME) "
                           "VALUES(%s, %s, %s)")
            cursor.executemany(replace_sql, results)
            conn.commit()
    except MySQLError as e:
        print(f"Error: {e}")

def update_rank():
    try:
        db = SQLHelper()
        with db as (conn, cursor):
            select_sql = ("SELECT a.LEVEL_NAME, ROUND(COUNT(*)/c.TICKER_COUNT*count(*), 1)  as proportion, a.UPDATE_TIME "
                          "FROM tendency a JOIN (SELECT TICKER_CODE FROM tendency ORDER BY DAY20_RISE DESC LIMIT 800 ) b "
                          "ON a.TICKER_CODE = b.TICKER_CODE JOIN category c on a.LEVEL_NAME = c.LEVEL_NAME "
                          "where TICKER_NAME not like '%st%' GROUP BY a.LEVEL_NAME order by proportion DESC")
            cursor.execute(select_sql)
            results = cursor.fetchall()

            data = list()
            for i in range(len(results)):
                rank_num = i + 1
                item = list(results[i])
                trade_day = item[-1].date()
                item.extend([rank_num,trade_day, shortuuid.uuid()])
                item.pop(1)
                data.append(item)

            replace_sql = ("replace into daily_rank(LEVEL_NAME, UPDATE_TIME, RANK_NUM, TRADE_DAY, RANK_ID) "
                           "VALUES(%s, %s, %s, %s, %s)")
            cursor.executemany(replace_sql, data)
            conn.commit()

    except MySQLError as e:
        print(f"Error: {e}")


def clear_current_table(table):
    try:
        db = SQLHelper()
        with db as (conn, cursor):
            clear_sql = "TRUNCATE TABLE " + table
            cursor.execute(clear_sql)
            conn.commit()
    except MySQLError as e:
        print(f"Error: {e}")

def update_tickers_from_txt(file):
    contents = ''
    # FastAPI's UploadFile is a file-like object, but we need to read its content as bytes and decode it.
    # Assuming the uploaded file has 'gbk' encoding as in the original code.
    with StringIO(file.read().decode('gbk')) as infile:
        for line in infile:
            stripped_line = line.rstrip()
            if stripped_line:
                processed_line = re.sub(r'\t\t', ',', stripped_line)
                processed_line = re.sub(r'\t', ',', processed_line)
                processed_line = re.sub(r'\+', '', processed_line)
                processed_line = re.sub(r'%', '', processed_line)
                contents += processed_line + '\n'

    eh = CsvHandler(StringIO(contents))
    df = eh.transfer_df()
    df_list = [(row['TICKER_CODE'], row['TICKER_NAME'], row['DAY20_RISE'], row['LIST_DATE'], row['LEVEL_NAME'],
                row['LEVEL_DETAIL'], row['FLOAT_MARKET_CAP'], row['UPDATE_TIME']) for index, row in df.iterrows()]
    try:
        db = SQLHelper()
        with db as (conn, cursor):
            sql_str = (
                "replace into tendency(TICKER_CODE, TICKER_NAME, DAY20_RISE, LIST_DATE, LEVEL_NAME, LEVEL_DETAIL, "
                "FLOAT_MARKET_CAP, UPDATE_TIME) VALUES(%s, %s, %s, %s, %s, %s, %s, %s)")
            cursor.executemany(sql_str, df_list)
            conn.commit()
    except Exception as e:
        print(e)


class RPSService(BaseService):
    def update_current_day_tickers(self):
        code_list = self.pro.stock_basic(list_status='L')['ts_code'].tolist()
        for ticker_code in code_list:
            ticker_name = self.pro.stock_basic(ts_code=ticker_code).at[0, 'name'] # stock name
            time.sleep(0.1)
            ticker_df = self.ts.pro_bar(ts_code=ticker_code, adj='qfq').head(20).reset_index(drop=True)
            time.sleep(0.1)
            current_close_price = ticker_df.at[0, 'close']
            pre_close_price = ticker_df.at[ticker_df.shape[0]-1, 'close']
            rise = round((current_close_price - pre_close_price) / pre_close_price * 100, 2) # 20 days rise
            time.sleep(0.1)
            # north bound share hold ratio
            hk_hold_df = self.pro.hk_hold(ts_code=ticker_code).head(1)
            if hk_hold_df.empty:
                nb_ratio = 0
            else:
                nb_ratio = hk_hold_df.at[0, 'ratio']
            time.sleep(0.1)
            list_date = self.pro.stock_basic(ts_code=ticker_code).at[0, 'list_date'] # list date
            time.sleep(0.1)
            category_df = self.pro.index_member_all(ts_code=ticker_code)
            l2_code = category_df.at[0, 'l2_code'] # level 2 industry category code
            l2_name = category_df.at[0, 'l2_name'] # level 2 industry category name
            update_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            try:
                db = SQLHelper()
                with db as (conn, cursor):
                    sql_str =  ("replace into tendency(TICKER_CODE, TICKER_NAME, DAY20_RISE, NB_RATIO, L2_CODE, L2_NAME, LIST_DATE, UPDATE_TIME) "
                                "VALUES('{}', '{}', {}, {}, '{}', '{}', STR_TO_DATE('{}', '%Y%m%d'), STR_TO_DATE('{}', '%Y-%m-%d %H:%i:%s'));".
                                format(ticker_code, ticker_name, rise, nb_ratio, l2_code, l2_name, list_date, update_time))
                    cursor.execute(sql_str)
                    conn.commit()
                    print('insert ' + ticker_code + ' success')
            except Exception as e:
                print(e)


if __name__ == '__main__':
    clear_current_table('tendency')
    print('tendency table clear success')
    # update_tickers_from_excel()
    update_tickers_from_txt("C:\\Users\\Lazy\\Downloads\\export\\Table.txt")
    print('tendency table update success')
    clear_current_table('category')
    print('category table clear success')
    update_category()
    print('category table update success')
    update_rank()
    print('daily_rank table update success')

